
package hk.ces.oxfam.patch.transaction;

import hk.ces.oxfam.patch.TransactionPatch;
import hk.ces.oxfam.patch.result.SQLResult;
import hk.ces.oxfam.patch.sql.PreparedSQL;
import hk.ces.oxfam.patch.sql.QueryUtil;
import hk.ces.oxfam.patch.util.POIHelper;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 *
 * @author ReutyCheng
 */
public class PersonProfilePatch implements TransactionPatch
{
  public void run(Connection connection) throws SQLException
  {
    Logger.getLogger(this.getClass()).debug("fetch all relationship");
    SQLResult relationshipResult = this.getRelationship(connection);
    List<Object[]> relationshipObjects = relationshipResult.getResultsList();

    Logger.getLogger(this.getClass()).debug("fetch each relationship");
    List<PersonProfileExcelRowItem> rowItems = new ArrayList<PersonProfileExcelRowItem>(relationshipObjects.size());
    for (Object[] objects : relationshipObjects)
    {
      Object[] personItem = this.getPersonItem(connection, (Long) objects[0]);
      Object[] profileItem = this.getProfileItem(connection, (Long) objects[1]);

      PersonProfileExcelRowItem rowItem = new PersonProfileExcelRowItem(personItem, profileItem);
      rowItems.add(rowItem);
    }

    Logger.getLogger(this.getClass()).debug("sort item");
    Collections.sort(rowItems, new Comparator<PersonProfileExcelRowItem>()
    {
      public int compare(PersonProfileExcelRowItem o1, PersonProfileExcelRowItem o2)
      {
        return (int) (o1.profileId - o2.profileId);
      }
    });
    
    Logger.getLogger(this.getClass()).debug("print out item");
    for (PersonProfileExcelRowItem rowItem : rowItems)
    {
      Logger.getLogger(this.getClass()).debug(rowItem.toString());
    }

    this.writeToExcel(rowItems);
  }

  private SQLResult getRelationship(Connection connection) throws SQLException
  {
    String sql = "SELECT * FROM PERSON_PERSONHISTORYPROFILE";
    return QueryUtil.query(connection, new PreparedSQL(sql, new Object[]{}));
  }

  private Object[] getPersonItem(Connection connection, Long personId) throws SQLException
  {
    String sql = "SELECT id, idtype, personalidno, personalidstate FROM PERSON WHERE id = ?";
    Object[] params = new Object[]{personId};
    return QueryUtil.results(connection, new PreparedSQL(sql, params), 0);
  }

  private Object[] getProfileItem(Connection connection, Long profileId) throws SQLException
  {
    String sql = "SELECT id, eventname, eventno, teamno, walkerno, elapsedtime, qualifiedlevel, referencechecked FROM personhistoryprofile WHERE id = ?";
    Object[] params = new Object[]{profileId};
    return QueryUtil.results(connection, new PreparedSQL(sql, params), 0);
  }

  private void writeToExcel(List<PersonProfileExcelRowItem> rowItems)
  {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Relationship between Person and Profile");

    int row = 0;
    HSSFRow headerRow = sheet.createRow(row++);
    POIHelper.createRow(headerRow, getHeader());

    for (PersonProfileExcelRowItem rowItem : rowItems)
    {
      HSSFRow objectRow = sheet.createRow(row++);
      POIHelper.createRow(objectRow, rowItem.getObjects());
    }

    try
    {
      FileOutputStream out = new FileOutputStream("C:\\relationship_of_person_and_profile.xls");
      workbook.write(out);
      out.close();
    }
    catch (FileNotFoundException ex)
    {
      Logger.getLogger(this.getClass()).error(ex.getMessage());
    }
    catch (IOException ex)
    {
      Logger.getLogger(this.getClass()).error(ex.getMessage());
    }
  }

  public static String[] getHeader()
  {
    return new String[]
    {
      "Person - Primary Key",
      "Person - HKID / PASSPORT",
      "Profile - Primary Key",
      "Profile - Event Name",
      "Profile - Event No.",
      "Profile - Team No.",
      "Profile - Walker No.",
      "Profile - Elapsed Time",
      "Profile - Qualified Level",
      "Profile - Reference Checked",
    };
  }

  public class PersonProfileExcelRowItem
  {
    public Long personId;
    public String personIdType;
    public String personPersonalIdNo;
    public String personCheckDigit;
    public Long profileId;
    public String profileEventName;
    public String profileEventNo;
    public String profileTeamNo;
    public String profileWalkerNo;
    public String profileElapsedTime;
    public int profileQualifiedLevel;
    public int profileReferenceChecked;

    public PersonProfileExcelRowItem(Object[] personItem, Object[] profileItem)
    {
      int index = 0;
      personId = (Long) personItem[index++];
      personIdType = (String) personItem[index++];
      personPersonalIdNo = (String) personItem[index++];
      personCheckDigit = (String) personItem[index++];

      index = 0;
      profileId = (Long) profileItem[index++];
      profileEventName = (String) profileItem[index++];
      profileEventNo = (String) profileItem[index++];
      profileTeamNo = (String) profileItem[index++];
      profileWalkerNo = (String) profileItem[index++];
      profileElapsedTime = (String) profileItem[index++];
      profileQualifiedLevel = (Integer) profileItem[index++];
      profileReferenceChecked = (Integer) profileItem[index++];
    }

    public Object[] getObjects()
    {
      return new Object[]
      {
        personId,
        new StringBuffer(16).append("[").append(personIdType).append("]").append(personPersonalIdNo).append("(").append(personCheckDigit).append(")").toString(),
        profileId,
        profileEventName,
        profileEventNo,
        profileTeamNo,
        profileWalkerNo,
        profileElapsedTime,
        profileQualifiedLevel,
        profileReferenceChecked
      };
    }

    @Override
    public String toString()
    {
      StringBuffer buffer = new StringBuffer(256);

      buffer.append("personId : ").append(personId).append(", ");
      buffer.append("personPersonalIdNo : ").append(new StringBuffer(16).append("[").append(personIdType).append("]").append(personPersonalIdNo).append("(").append(personCheckDigit).append(")").toString()).append(", ");
      buffer.append("profileId : ").append(profileId).append(", ");
      buffer.append("profileEventName : ").append(profileEventName).append(", ");
      buffer.append("profileEventNo : ").append(profileEventNo).append(", ");
      buffer.append("profileTeamNo : ").append(profileTeamNo).append(", ");
      buffer.append("profileWalkerNo : ").append(profileWalkerNo).append(", ");
      buffer.append("profileElapsedTime : ").append(profileElapsedTime).append(", ");
      buffer.append("profileQualifiedLevel : ").append(profileQualifiedLevel).append(", ");
      buffer.append("profileReferenceChecked : ").append(profileReferenceChecked);

      return buffer.toString();
    }
  }
}
